const express = require('express');

const routes = express.Router();

//引入mysql
const mysql = require('mysql');
//创建连接池
var pool = mysql.createPool({
    connectionLimit : 10,
    host : 'localhost',
    user : 'root',
    password : 'root',
    database : 'stumsg'
})

routes.get('/',(req,resp)=>{
    //获取cookies
    var uid = req.cookies.uid;
    var gender = req.cookies.gender;

    //判断
    if(gender == 1){
        var pname = '教师';
    }else {
        var pname = '学生';
    }

    var sql = `SELECT scores.uid , scores.scores , sub_class.class_name , sub_class.sub_id , sub_class.num FROM scores LEFT JOIN sub_class ON scores.class_id = sub_class.id WHERE scores.uid = ${uid}`;

    var sqll = `SELECT stu_list.* , \`subject\`.sub_name FROM stu_list LEFT JOIN \`subject\` ON stu_list.sub_id = \`subject\`.id WHERE stu_list.uid = ${uid}`;

    pool.getConnection(function(err,connection){
        if(err) throw err;
        //    链接成功
        connection.query(sql,(error,result)=> {
           // console.log(result)
            //将数组重组成一个二维数组
            //声明一个空格的数组 一维数组
            var scores = [];

            //有五个学期  所以确认需要循环五次la
            for(var i = 0; i < 5; i++){
                if( !scores[i] ) scores[i] = [];
            //    循环result
                result.forEach( (item)=>{
                    //符合条件就进行添加到当前下标
                    if( item.num == (i+1) ){
                        scores[i].push(item);
                    }
                } )
            }

            //console.log(result)
            connection.query(sqll,(errs,res)=>{
                //    释放连接   关闭数据库
                //connection.release();
                //console.log(res);
                resp.render('home',{
                    uid : uid,
                    pname : pname,
                    scores : scores,
                    res : res
                })
                if(errs) throw errs;
            })

            //sql语句出错执行报错处理
            if(error) throw error;
        })
    })


})
    .post('/',(req,resp)=>{

    })

//暴露接口
module.exports = routes;

// SELECT stu_list.* , `subject`.sub_name FROM stu_list LEFT JOIN `subject` ON stu_list.sub_id = `subject`.id WHERE stu_list.uid = 1901